const db = require('../config/db');
const { sendError } = require('../utils/response');

module.exports = {
  // 获取所有商品（带分页和筛选）
  async getAllProducts({ category_id, is_featured, keyword, status, page = 1, limit = 10 }) {
    const offset = (page - 1) * limit;

    let query = 'SELECT p.*, pc.name_en as category_name_en, pc.name_cn as category_name_cn FROM products p ';
    query += 'LEFT JOIN product_categories pc ON p.category_id = pc.id WHERE 1=1';
    const params = [];

    if (category_id) {
      query += ' AND p.category_id = ?';
      params.push(category_id);
    }

    if (is_featured !== undefined) {
      query += ' AND p.is_featured = ?';
      params.push(is_featured ? 1 : 0);
    }

    if (keyword) {
      query += ' AND (p.name LIKE ? OR p.description LIKE ?)';
      params.push(`%${keyword}%`, `%${keyword}%`);
    }

    if (status) {
      query += ' AND p.status = ?';
      params.push(status);
    }

    query += ' ORDER BY p.created_at DESC LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));

    const [products] = await db.execute(query, params);

    // 获取总数用于分页
    let countQuery = 'SELECT COUNT(*) as total FROM products p WHERE 1=1';
    const countParams = params.slice(0, -2); // 移除LIMIT和OFFSET参数

    if (category_id) {
      countQuery += ' AND p.category_id = ?';
    }

    if (is_featured !== undefined) {
      countQuery += ' AND p.is_featured = ?';
    }

    if (keyword) {
      countQuery += ' AND (p.name LIKE ? OR p.description LIKE ?)';
    }

    if (status) {
      countQuery += ' AND p.status = ?';
    }

    const [total] = await db.execute(countQuery, countParams);

    // 获取每个商品的评论数和平均评分
    for (const product of products) {
      const [reviews] = await db.execute(
        'SELECT COUNT(*) as count, AVG(rating) as avg_rating FROM reviews WHERE product_id = ?',
        [product.id]
      );

      product.reviews_count = reviews[0].count || 0;
      product.average_rating = reviews[0].avg_rating ? parseFloat(reviews[0].avg_rating) : 0;
    }

    return {
      products,
      pagination: {
        total: total[0].total,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(total[0].total / limit)
      }
    };
  },

  // 获取商品详情
  async getProductById(id) {
    const [products] = await db.execute(
      "select * from products where id =?", [id]
    );

    if (products.length === 0) {
      throw new Error('商品不存在');
    }

    const product = products[0];

    // 获取商品评论统计
    const [reviews] = await db.execute(
      'SELECT COUNT(*) as count, AVG(rating) as avg_rating FROM reviews WHERE product_id = ?',
      [id]
    );

    product.reviews_count = reviews[0].count || 0;
    product.average_rating = reviews[0].avg_rating ? parseFloat(reviews[0].avg_rating) : 0;

    // 获取商家信息
    const [merchant] = await db.execute(
      'SELECT id, username, avatar FROM users WHERE id = ?',
      [product.merchant_id]
    );

    if (merchant.length > 0) {
      product.merchant = merchant[0];
    }

    return product;
  },

  async getProductsByMerchantId(merchantId, productId) {
    const [products] = await db.execute(
      'SELECT * FROM products WHERE merchant_id =? AND id =?',
      [merchantId, productId]
    );
    return products;
  },
  // 商家创建商品
  async createProduct(merchantId, { name, description, price, stock, category_id, is_featured, images }) {
    if (!name || !price || stock === undefined) {
      throw new Error('缺少必要参数');
    }

    if (price <= 0) {
      throw new Error('价格必须大于0');
    }

    if (stock < 0) {
      throw new Error('库存不能为负数');
    }

    const [result] = await db.execute(
      'INSERT INTO products (merchant_id, name, description, price, stock, category_id, images, is_featured) ' +
      'VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [merchantId, name, description, price, stock, category_id || null, images || '', is_featured ? 1 : 0]
    );

    return result.insertId;
  },

  // 商家更新商品
  async updateProduct(merchantId, productId, { name, description, price, stock, category_id, is_featured, status, images }) {
    // 检查商品是否属于该商家
    const [products] = await db.execute(
      'SELECT id FROM products WHERE id = ? AND merchant_id = ?',
      [productId, merchantId]
    );

    if (products.length === 0) {
      throw new Error('商品不存在或无权修改');
    }

    // 构建更新字段
    const fields = [];
    const params = [];

    if (name !== undefined) {
      fields.push('name = ?');
      params.push(name);
    }

    if (description !== undefined) {
      fields.push('description = ?');
      params.push(description);
    }

    if (price !== undefined) {
      if (price <= 0) {
        throw new Error('价格必须大于0');
      }
      fields.push('price = ?');
      params.push(price);
    }

    if (stock !== undefined) {
      if (stock < 0) {
        throw new Error('库存不能为负数');
      }
      fields.push('stock = ?');
      params.push(stock);
    }

    if (category_id !== undefined) {
      fields.push('category_id = ?');
      params.push(category_id || null);
    }

    if (is_featured !== undefined) {
      fields.push('is_featured = ?');
      params.push(is_featured ? 1 : 0);
    }

    if (status !== undefined) {
      fields.push('status = ?');
      params.push(status);
    }

    if (images !== undefined) {
      fields.push('images = ?');
      params.push(images);
    }

    if (fields.length === 0) {
      throw new Error('没有可更新的字段');
    }

    fields.push('updated_at = CURRENT_TIMESTAMP');
    params.push(productId);

    const query = `UPDATE products SET ${fields.join(', ')} WHERE id = ?`;
    const [result] = await db.execute(query, params);

    return result.affectedRows > 0;
  },

  // 商家删除商品
  async deleteProduct(merchantId, productId) {
    // 检查商品是否属于该商家
    const [result] = await db.execute(
      'DELETE FROM products WHERE id = ? AND merchant_id = ?',
      [productId, merchantId]
    );

    if (result.affectedRows === 0) {
      throw new Error('商品不存在或无权删除');
    }

    return true;
  },

  // 获取商家的商品列表
  async getProductsByMerchant(merchantId, { status, page = 1, limit = 10 }) {
    const offset = (page - 1) * limit;

    let query = 'SELECT * FROM products WHERE merchant_id = ?';
    const params = [merchantId];

    if (status) {
      query += ' AND status = ?';
      params.push(status);
    }

    query += ' ORDER BY created_at DESC LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));

    const [products] = await db.execute(query, params);

    // 获取总数用于分页
    let countQuery = 'SELECT COUNT(*) as total FROM products WHERE merchant_id = ?';
    const countParams = [merchantId];

    if (status) {
      countQuery += ' AND status = ?';
      countParams.push(status);
    }

    const [total] = await db.execute(countQuery, countParams);

    return {
      products,
      pagination: {
        total: total[0].total,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(total[0].total / limit)
      }
    };
  },

  // 更新商品库存
  async updateStock(productId, quantity) {
    const [result] = await db.execute(
      'UPDATE products SET stock = stock + ? WHERE id = ?',
      [quantity, productId]
    );

    if (result.affectedRows === 0) {
      throw new Error('商品不存在');
    }

    return true;
  },

  // 获取推荐商品（首页轮播）
  async getFeaturedProducts(limit = 10) {
    const [products] = await db.execute(
      'SELECT p.*, pc.name_en as category_name_en, pc.name_cn as category_name_cn FROM products p ' +
      'LEFT JOIN product_categories pc ON p.category_id = pc.id ' +
      'WHERE p.is_featured = 1 AND p.status = "active" ' +
      'ORDER BY p.created_at DESC LIMIT ?',
      [limit]
    );

    // 获取每个商品的评论数和平均评分
    for (const product of products) {
      const [reviews] = await db.execute(
        'SELECT COUNT(*) as count, AVG(rating) as avg_rating FROM reviews WHERE product_id = ?',
        [product.id]
      );

      product.reviews_count = reviews[0].count || 0;
      product.average_rating = reviews[0].avg_rating ? parseFloat(reviews[0].avg_rating) : 0;
    }

    return products;
  },

  // 检查商品是否属于商家
  async checkProductOwnership(merchantId, productId) {
    const [products] = await db.execute(
      'SELECT id FROM products WHERE id = ? AND merchant_id = ?',
      [productId, merchantId]
    );

    return products.length > 0;
  }
};